package com.ylfstu.jdbc;

import com.mysql.jdbc.Driver;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @Author: Joy_yang
 * @Date: 2022/10/19 10:10
 * 连接mysql五种方式
 */
public class JdbcConn {

    //方式一
    @Test
    public void connect01() throws SQLException {
        Driver driver = new Driver();
        String url = "jdbc:mysql://localhost:3306/ylf_db02?&useSSL=false";
        Properties properties = new Properties();
        properties.setProperty("user","root");//用户
        properties.setProperty("password","ylf");//密码
        Connection connect = driver.connect(url, properties);
        System.out.println("方式一：" + connect);
    }

    //方式二
    @Test
    public void connect02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //使用反射加载Driver类 动态加载 更加灵活，减少依赖性
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/ylf_db02?&useSSL=false";
        Properties properties = new Properties();
        properties.setProperty("user","root");//用户
        properties.setProperty("password","ylf");//密码
        Connection connect = driver.connect(url, properties);
        System.out.println("方式二：" + connect);
    }

    //方式三 使用DriverManager 替代 Driver 进行统一管理
    @Test
    public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //使用反射加载Driver
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver  = (Driver) aClass.newInstance();

        //创建url 和 user 和 password
        String url = "jdbc:mysql://localhost:3306/ylf_db02?&useSSL=false";
        String user = "root";
        String password = "ylf";

        DriverManager.registerDriver(driver); //注册Driver驱动
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("方式三：" + connection);

    }

    //方式四 使用Class.forName 自动完成注册驱动，简化代码
    //推荐使用这种方法
    @Test
    public void connect04() throws ClassNotFoundException, SQLException {
        //使用反射加载Driver
        //在加载Driver时， 自动完成注册
        /*
        1.静态代码块，在类加载时，会执行一次
        2.DriverManager.registerDriver(new Driver());
        3.因此注册Driver的工作已经完成
             static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");
                }
             }
        */
        Class.forName("com.mysql.jdbc.Driver");

        //创建url 和 user 和 password
        String url = "jdbc:mysql://localhost:3306/ylf_db02?&useSSL=false";
        String user = "root";
        String password = "ylf";

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("方式四：" + connection);
    }
        //提示：
        //1.mysql驱动5.1.6可以以无需Class.forName("com.mysql.jdbc.Driver");
        //2.从jdk1.5以后使用了jdbc4，不再需要显式调用Class.forName()注册驱动，
        // 而是自动调用驱动jar包下META-INF\services\java.sql.Driver文本中的类名称去注册
        //3.建议写上 Class.forName("com.mysql.jdbc.Driver"); 更明确

    //方式五 在方式四的基础上改进，增加配置文件，让连接mysql更加灵活
    //用第五种！！！
    @Test
    public void connect05() throws IOException, ClassNotFoundException, SQLException {
        //通过Properties对象获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);//可以不写

        Connection connection = DriverManager.getConnection(url, user, password);

        System.out.println("方式五：" + connection);

    }

    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {

        //1~2 注册驱动，得到连接
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);

        //3.执行SQL
        String sql1 = "insert into news values(null, '中证报头版：央地政策齐发力 稳定制造业投资')";
        String sql2 = "insert into news values(null, '公安部：中国成为世界上最安全国家之一')";
        String sql3 = "insert into news values(null, '刚上任44天的首相已被“架空”，英国未来路在何方？')";
        String sql4 = "insert into news values(null, '应对能源短缺 德国总理要求延长三座核电站运转')";
        String sql5 = "insert into news values(null, '特拉斯的关键周三：紧缩政策或激怒选民 保守党后座会议即将开始')";
        String sql6 = "update news set content = '俄媒：德国军队计划帮助训练5000名乌克兰军人' where id = 1 ";
        String sql7 = "delete from news where id = 3";

        Statement statement = connection.createStatement();
        int i1 = statement.executeUpdate(sql1);
        int i2 = statement.executeUpdate(sql2);
        int i3 = statement.executeUpdate(sql3);
        int i4 = statement.executeUpdate(sql4);
        int i5 = statement.executeUpdate(sql5);
        int i6 = statement.executeUpdate(sql6);
        int i7 = statement.executeUpdate(sql7);

        System.out.println(i1 > 0 ? "sql1执行成功" : "1执行失败");
        System.out.println(i2 > 0 ? "sql2执行成功" : "2执行失败");
        System.out.println(i3 > 0 ? "sql3执行成功" : "3执行失败");
        System.out.println(i4 > 0 ? "sql4执行成功" : "4执行失败");
        System.out.println(i5 > 0 ? "sql5执行成功" : "5执行失败");
        System.out.println(i6 > 0 ? "sql6执行成功" : "6执行失败");
        System.out.println(i7 > 0 ? "sql7执行成功" : "7执行失败");

        //4.关闭连接
        statement.close();
        connection.close();
    }



}
